I'm trying to enforce consistency in a Excel table using Data validation (yes I know. Not my project but I deal with it). The table to validate has ever-increasing specificity, columns starting with "Campus" then drilling down to "Building", then "Floor", "Cube" etc. Call it WalkDiscovered The validation source is a 1nf splat of said campus, buildings, floors, in identically-named columns. Call it WalkLocations. The goal is for Excel to, based on the current-editing-cell's header, determine what list of values should exist there from the prior column. At the moment, assuming there is only one group of specificity (i.e. I don't need the prior columns included (yet), just the prior column). Here is my current attempt at a formula to accomplish this: Named Range: listLocData =UNIQUE(FILTER( INDEX( WalkLocations ,0 ,MATCH( INDEX( WalkDiscovered[#Headers] ,1 ,COLUMN() ) ,WalkLocations[#Headers] ,0 ) ) ,INDEX( WalkLocations ,0 ,MATCH( INDEX( WalkDiscovered[#Headers] ,1 ,COLUMN() ) ,WalkLocations[#Headers] ,0 )-1)=INDEX( WalkDiscovered[@] ,1 ,COLUMN()-1 ) )) And to try to make it data-validation-friendly: Name Range: locDropDown =INDEX(listLocData,1,1):INDEX(listLocData,COUNTA(listLocData)) Not sure on that second part. It appears to generate the correct list if I just put =locDropDown into an arbitrary cell and evaluate it (obviously spilling isn't allowed in tables, but that's not important) but totally fails when putting the same as the Data Validation formula. Any ideas/hints/suggestions?